package com.taxinfo.service.impl;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.taxinfo.component.alert.MyAlert;
import com.taxinfo.dao.*;
import com.taxinfo.pojo.dto.*;
import com.taxinfo.pojo.vo.*;
import com.taxinfo.service.AccBalMapInfoService;
import com.taxinfo.service.AssetAndLibService;
import com.taxinfo.service.IncomeStmInfoService;
import com.taxinfo.service.MainReportService;
import com.taxinfo.utils.DateUtil;
import javafx.scene.control.Alert;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author wanghy
 * @since 2025-01-20
 */
@Service("MainReportServiceImpl")
public class MainReportServiceImpl extends ServiceImpl<MainReportMapper, MainReport> implements MainReportService {
    @Autowired
    private MainReportMapper mainReportMapper;

    @Autowired
    private AccBalMapInfoService accBalMapInfoService;

    @Autowired
    private SubMatchingMapper subMatchingMapper;

    @Autowired
    private StandardSubMapper standardSubMapper;

    @Autowired
    private DecDocumentRecMapper decDocumentRecMapper;

    @Autowired
    private SoftwareMapModelMapper softwareMapModelMapper;

    @Autowired
    private AssetAndLibMapper assetAndLibMapper;

    @Autowired
    private IncomeStmInfoMapper incomeStmInfoMapper;

    @Autowired
    private AssetAndLibService assetAndLibService;

    @Autowired
    private IncomeStmInfoService incomeStmInfoService;

    private Map<String,String> fileMap = new HashMap<String,String>();

    @Value("${modelFile.year}")
    private String modelYearFilePath;
    @Value("${modelFile.month}")
    private String modelMonthFilePath;
    @Value("${modelFile.quarter}")
    private String modelQuarterFilePath;

    @Value("${targetFile.year}")
    private String targetYearFilePath;
    @Value("${targetFile.month}")
    private String targetMonthFilePath;
    @Value("${targetFile.quarter}")
    private String targetQuarterFilePath;

    @Value("${modelFile.subMapMode}")
    private String subMapMode;

    @Override
    public List<MainReportDto> selectAllData() {
        return mainReportMapper.selectAllData();
    }

    @Override
    public List<MainReportDto> selectDataByCondition(String decYear, String pSubType){
        return mainReportMapper.selectDataByCondition(decYear, pSubType);
    }

    /**
     * 主数据处理
     * @param decYear
     * @param pSubType
     * @return
     */
    private String mainDataAction(String decYear, String pSubType,boolean flg){
        LambdaQueryWrapper<MainReport> lambadaQueryWrapper = new LambdaQueryWrapper();
        lambadaQueryWrapper.eq(MainReport::getDecYear, decYear);
        List<MainReport> mainReports = mainReportMapper.selectList(lambadaQueryWrapper);
        String id = "";
        if(mainReports.size()>0){
            id = mainReports.get(0).getId();
            if(flg){
                // 删除旧数据
                LambdaQueryWrapper<AccBalMapInfo> wrapperDelete = new LambdaQueryWrapper();
                wrapperDelete.eq(AccBalMapInfo::getReportId, id);
                accBalMapInfoService.getBaseMapper().delete(wrapperDelete);
            }else{
                LambdaQueryWrapper<AssetAndLib> wrapperAssetAndLib = new LambdaQueryWrapper();
                wrapperAssetAndLib.eq(AssetAndLib::getReportId, id);
                assetAndLibMapper.delete(wrapperAssetAndLib);
                LambdaQueryWrapper<IncomeStmInfo> wrapperIncomeStmInfo = new LambdaQueryWrapper();
                wrapperIncomeStmInfo.eq(IncomeStmInfo::getReportId, id);
                incomeStmInfoMapper.delete(wrapperIncomeStmInfo);
            }

        }else{
            id =  mainReportMapper.getMaxReportID();
            MainReport mainReport = new MainReport();
            mainReport.setDecYear(decYear);
            mainReport.setId(id);
            mainReport.setSbType(pSubType);
            mainReport.setAccBalName(decYear);
//            if(flg){
//                mainReport.setAccBalName("科目余额表");
//            }else{
//                mainReport.setAccBalName("会计准则");
//            }

            mainReportMapper.insert(mainReport);
        }
        return id;
    }
    @Override
    public String impBalanceData(File file,String decYear,String pSubType) {
        String rtrVal = "";
        InputStream fis = null;
        Workbook workbook = null;
        //导入主数据
        String id = this.mainDataAction(decYear,pSubType,true);
        try {
            if (null == file){
                return "文件未选择！";
            }
            fis = new FileInputStream(file);
            workbook = WorkbookFactory.create(fis);
            Sheet sheet = workbook.getSheetAt(0);

            // 定位信息获取
            SoftwareMapModel softwareMapModel = softwareMapModelMapper.selectOne(new QueryWrapper<>());
            List<SubMatchingDto> subMatchingDtos =  subMatchingMapper.selectAllData("余额表科目");
            Map<String, SubMatchingDto> subMatchingMap = subMatchingDtos.stream().collect(Collectors.toMap(SubMatchingDto::getCompSubNo, subMatchingDto ->subMatchingDto));
            //模版匹配处理
            List<AccBalMapInfo> accBalMapInfos  =  this.getRowColumInfo(softwareMapModel,sheet,subMatchingMap);
            if(null == accBalMapInfos) {
                rtrVal = "模版匹配出错，请检查模版格式是否正确！";
                return rtrVal;
            }
            //数据映射
            List<AccBalMapInfo> accBalMapInfoOks = new ArrayList<>();
            int intNew = 0;
            QueryWrapper wrapper = new QueryWrapper<>();
            wrapper.eq("dec_type", "余额表科目");
            List<StandardSub> standardSubs = standardSubMapper.selectList(wrapper);
            Map<String, StandardSub> standardSubMap = standardSubs.stream().collect(Collectors.toMap(StandardSub::getSubName, standardSub -> standardSub));

            for (AccBalMapInfo accBalMapInfo : accBalMapInfos) {
                String result[] =  this.checkMapInfo(accBalMapInfo.getCompSubNo(),accBalMapInfo.getCompSubName(),subMatchingMap,standardSubMap);
                if(null != result){
                    if("new".equals(result[3])){
                        intNew ++;
                    }
                    //组装集合
                    if(!"禁用".equals(result[2])){
                        accBalMapInfo.setSubNo(result[0]);
                        accBalMapInfo.setSubName(result[1]);
                        accBalMapInfo.setSubType(result[4]);
                        accBalMapInfo.setReportId(id);
                        accBalMapInfoOks.add(accBalMapInfo);
                    }
                }

            }
            accBalMapInfoService.saveBatch(accBalMapInfoOks);
            if(intNew == 0) {
                rtrVal ="0科目余额表导入成功,企业无新增科目!";
            }else{
                rtrVal ="0科目余额表导入成功,企业新增科目【".concat(String.valueOf(intNew)).concat("条】,请在前往科目匹配功能中核对!");
            }

        } catch (Exception e) {
            e.printStackTrace();
            rtrVal = "导入数据失败";
        }finally {
            try {
                workbook.close();
                workbook = null;
                fis.close();
                fis = null;
            } catch (IOException e) {
                e.printStackTrace();
                rtrVal = "关闭Excel文件失败";
            }
        }
        return rtrVal;
    }


    /**
     * 检查对比科目映射
     *
     * @param compSubNo
     * @param compSubName
     * @return
     */
    private String[] checkMapInfo(String compSubNo, String compSubName,Map<String, SubMatchingDto> subMatchingMap,Map<String, StandardSub> standardSubMap) {
        String[] result = new String[5];
        SubMatchingDto subMatchingDto = subMatchingMap.get(compSubNo);
        if(null != subMatchingDto){
            if(StringUtils.isNotBlank(subMatchingDto.getCompSubName())){
                // && strCompSubName.equals(compSubName)){
                result[0]= subMatchingDto.getSubNo();
                result[1] = subMatchingDto.getSubName();
                result[2] = subMatchingDto.getSwitchFlg();
                result[3] = "old";
                result[4] = subMatchingDto.getSubType();

            }else{
                result=  checkSwitch(standardSubMap,subMatchingDto.getCompSubNo(),subMatchingDto.getCompSubName().trim());
            }
        }else{
            result = this.checkSwitch(standardSubMap,compSubNo, compSubName.trim());
        }
        return result;
    }

    private String[] checkSwitch(Map<String, StandardSub> standardSubMap,String compSubNo, String compSubName){
        if(StringUtils.isBlank(compSubNo) || StringUtils.isBlank(compSubName)){
            return null;
        }
        String[] result = new String[5];

        SubMatching subMatching = new SubMatching();
        subMatching.setDecType("余额表科目");
        if(compSubName.contains(compSubNo)){
            compSubName = compSubName.substring(compSubNo.length()+1);
        }
        String[] comSubName = getSpecialCharsBySplit(compSubName);
        if(comSubName.length ==1){
            StandardSub standardSub = standardSubMap.get(comSubName[0].trim());
            if(null != standardSub){
                subMatching.setSubNo(standardSub.getSubNo());
                subMatching.setSubName(standardSub.getSubName());
                subMatching.setSubType(standardSub.getSubType());
            }else{
                subMatching.setSubNo("");
                subMatching.setSubName("");
                subMatching.setSubType("");
            }
        }else{
            subMatching.setSubNo("");
            subMatching.setSubName("");
            subMatching.setSubType("");
            LambdaQueryWrapper<StandardSub> wrapper1 = new LambdaQueryWrapper<StandardSub>();
            wrapper1.likeRight(StandardSub::getSubName,comSubName[0]);
            wrapper1.like(StandardSub::getSubName,comSubName[comSubName.length - 1]);
            List<StandardSub> standardSubs1 = standardSubMapper.selectList(wrapper1);
            if(null != standardSubs1 ) {
                if(standardSubs1.size() == 1){
                    String[] subArr =  this.getSpecialCharsBySplit(standardSubs1.get(0).getSubName());
                    if(subArr.length > 1){
                        subMatching.setSubNo(standardSubs1.get(0).getSubNo());
                        subMatching.setSubName(standardSubs1.get(0).getSubName());
                        subMatching.setSubType(standardSubs1.get(0).getSubType());
                    }
                }else {
                    if("医疗保险".equals(comSubName[comSubName.length - 1])){
                        for(StandardSub standard : standardSubs1){
                            String[] subArr =  this.getSpecialCharsBySplit(standard.getSubName());
                            if(subArr.length>1 && standard.getSubName().contains("基本医疗保险")){
                                subMatching.setSubNo(standardSubs1.get(0).getSubNo());
                                subMatching.setSubName(standardSubs1.get(0).getSubName());
                                subMatching.setSubType(standardSubs1.get(0).getSubType());
                            }
                        };
                    }
                    if("养老保险".equals(comSubName[comSubName.length - 1])){
                        for(StandardSub standard : standardSubs1){
                            String[] subArr =  this.getSpecialCharsBySplit(standard.getSubName());
                            if(subArr.length>1 && standard.getSubName().contains ("基本养老保险")){
                                subMatching.setSubNo(standardSubs1.get(0).getSubNo());
                                subMatching.setSubName(standardSubs1.get(0).getSubName());
                                subMatching.setSubType(standardSubs1.get(0).getSubType());
                            }
                        };
                    }
                }
            }
        }
        subMatching.setCompSubNo(compSubNo);
        subMatching.setCompSubName(compSubName);
        if (StringUtils.isBlank(subMatching.getSubName()) && StringUtils.isBlank(subMatching.getSubNo())) {
            subMatching.setSwitchFlg("禁用");
        } else {
            subMatching.setSwitchFlg("启用");
        }
        subMatching.setStatus("1");
        subMatchingMapper.insert(subMatching);
        result[0]= subMatching.getSubNo();
        result[1] = subMatching.getSubName();
        result[2] = subMatching.getSwitchFlg();
        result[3] = "new";
        result[4] = subMatching.getSubType();
        return result;
      }
    private String transformation(Object obj,String type){
        if(null == obj || "".equals(String.valueOf(obj))){
            if("NUMERIC".equals(type)){
                return "0";
            }else{
                return "";
            }
        }else{
            return String.valueOf(obj);
        }
    }

    private String[] getSpecialCharsBySplit(String input){
        // 定义一个正则表达式，匹配非字母数字字符
        String regex = "[^\\p{Alpha}\\p{Digit}\\u4e00-\\u9fa5]";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        StringBuilder specialChars = new StringBuilder();
        String split = "";
        while (matcher.find()) {
            System.out.println("Special characters: " + matcher.group());
            split = matcher.group();
            if("\\".equals(matcher.group())){
                split = "\\\\";
            }
            break;
        }
        if("".equals(split)){
            return new String[]{input};
        }else{
            String[] rtnVal  = input.split(split);
            return rtnVal;
        }
    }

    @Override
    public String createDecFile(String id, String taxNo, String taxName, String sbYear, String sblx, String fileName) {
        this.initMapInfo();
        //获取模版路径
        String filePath = "";
        if("年度".equals(sblx)){
            filePath = modelYearFilePath.concat(File.separator).concat(fileName);
        }else if("月度".equals(sblx)){
            filePath = modelMonthFilePath.concat(File.separator).concat(fileName);
        }else if("季度".equals(sblx)){
            filePath = modelQuarterFilePath.concat(File.separator).concat(fileName);
        }

        String jarPath = System.getProperty("java.class.path");
        String currentPath = "";
        if(jarPath.endsWith(".jar" ) && !jarPath.contains(";")){
            String jarDirectory = new File(jarPath).getAbsolutePath();
            currentPath = jarDirectory.substring(0,jarDirectory.lastIndexOf("\\"));
            filePath = currentPath.concat(filePath);
        }else{
            try {
                currentPath = new File(".").getCanonicalPath();
            } catch (IOException e) {
                e.printStackTrace();
            }
            filePath = currentPath.concat(filePath);
        }

        Path path = Paths.get(filePath);
        String  clazz = this.fileMap.get(sblx.concat(fileName));
        // 检查文件是否存在
        if (Files.exists(path)) {
            XSSFWorkbook wb = null;
            FileInputStream is = null;
            FileOutputStream fileOut = null;
            try {
                //目标文件处理
                is = new FileInputStream(new File(filePath));
                wb = new XSSFWorkbook(is);
                Map<String,List> dataInfos  = new HashMap<>();
                //财务报表
                if(fileName.contains("财务报表")){
                    Wrapper query = new QueryWrapper();
                    String key = getLastDataPoint(sbYear,sblx);
                    List<AssetAndLibDto>  assetMapInfos = assetAndLibService.selectDataByParentId(id);
                    List<IncomeStmInfoDto>  incomMapInfos = incomeStmInfoService.selectDataByParentId(id);
                    List<IncomeStmInfoDto> lastIncomeDataInfo = incomeStmInfoService.selectLastDataInfo(key);
                    dataInfos.put("assetMapInfos",assetMapInfos);
                    dataInfos.put("incomMapInfos",incomMapInfos);
                    dataInfos.put("lastIncomeDataInfo",lastIncomeDataInfo);
                }else{
                    Wrapper query = new QueryWrapper();
                    String key = getLastDataPoint(sbYear,sblx);
                    List<AccBalMapInfoDto>  accBalMapInfos = accBalMapInfoService.selectDataByParentId(id);
                    List<AccBalMapInfoDto> lastAccDataInfo = accBalMapInfoService.selectLastDataInfo(key);
                    List<IncomeStmInfoDto>  incomMapInfos = incomeStmInfoService.selectDataByParentId(id);
                    List<IncomeStmInfoDto> lastIncomeDataInfo = incomeStmInfoService.selectLastDataInfo(key);
                    dataInfos.put("accBalMapInfos",accBalMapInfos);
                    dataInfos.put("lastAccDataInfo",lastAccDataInfo);
                    dataInfos.put("incomMapInfos",incomMapInfos);
                    dataInfos.put("lastIncomeDataInfo",lastIncomeDataInfo);
                }

                // 处理模版
                Class<?> cls = Class.forName(clazz);
                Method method = cls.getDeclaredMethod("businessLogic", XSSFWorkbook.class,Map.class,String.class,String.class);
                Object obj =  cls.getDeclaredConstructor().newInstance();
                Object result = method.invoke(obj, wb, dataInfos,sblx, sbYear);
                if (null != result && !"".equals(result.toString())){
                    new MyAlert(Alert.AlertType.WARNING, "标准科目【".concat(result.toString()).concat("】映射关系缺失,请检查!")).show();
                }

                fileName = sbYear.concat("-").concat(fileName);
                String obJFilePath ="";
                if("年度".equals(sblx)){
                    obJFilePath = targetYearFilePath.concat(File.separator).concat(fileName);
                }else if("月度".equals(sblx)){
                    obJFilePath = targetMonthFilePath.concat(File.separator).concat(fileName);
                }else if("季度".equals(sblx)){
                    obJFilePath = targetQuarterFilePath.concat(File.separator).concat(fileName);
                }

                // 创建FormulaEvaluator并计算公式
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                evaluator.evaluateAll();
                // 保存修改后的工作簿到新文件
                if(StringUtils.isNotBlank(obJFilePath)){
                    filePath = currentPath.concat(obJFilePath);
                    fileOut = new FileOutputStream(filePath);
                    wb.write(fileOut);
                }else{
                    new MyAlert(Alert.AlertType.ERROR, "目标文件文件未生成，请检查系统配置！").show();
                    return "";
                }
             } catch (FileNotFoundException e) {
                filePath = "";
                e.printStackTrace();
                new MyAlert(Alert.AlertType.ERROR, fileName.concat(" 文件已被打开，请先关闭此文件！")).show();
            } catch (IOException e) {
                filePath="";
                new MyAlert(Alert.AlertType.ERROR, "模版文件IO读取失败！").show();
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                filePath="";
                e.printStackTrace();
            } catch (InstantiationException e) {
                filePath="";
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                filePath="";
                e.printStackTrace();
            } catch (NoSuchMethodException e) {
                filePath="";
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                filePath="";
                e.printStackTrace();
            } finally {
                try {
                    if (is!= null) {
                        is.close();
                    }
                    if (wb!= null) {
                        wb.close();
                    }
                    if(null != fileOut){
                        fileOut.close();
                    }
                } catch (IOException e) {
                    new MyAlert(Alert.AlertType.ERROR, "关闭文件异常！").show();
                    e.printStackTrace();
                }
            }

        } else {
            new MyAlert(Alert.AlertType.ERROR, "模版文件不存在！").show();
            return null;
        }
       return filePath;
    }

    /**
     * 获取上次申报条件
     * @param sbYear
     * @param sblx
     * @return
     */
    private String getLastDataPoint(String sbYear, String sblx) {
        if("年度".equals(sblx)){
            String year = String.valueOf(Integer.parseInt(sbYear.substring(0,4))-1).concat("年");
            return year;
        }else if("季度".equals(sblx)){
            int quarter = Integer.parseInt(sbYear.substring(5,6));
            String year = "";
            if(quarter == 1){
                quarter = 4;
                year = String.valueOf(Integer.parseInt(sbYear.substring(0,4))-1);
            }else{
                quarter--;
                year = sbYear.substring(0,4);
            }
            return year.concat("年".concat(String.valueOf(quarter))).concat("季度");
        }else if("月度".equals(sblx)){
            String year = sbYear.substring(0,4);
            int month = Integer.parseInt(sbYear.substring(5,sbYear.length()-1));
            if(month == 1){
                month = 12;
                year = String.valueOf(Integer.parseInt(year)-1);
            }else{
                month--;
            }
            return sbYear.substring(0,4).concat("年".concat(String.valueOf(month))).concat("月");
        }else{
            return "";
        }
    }

    @Override
    public void saveFileRecord(String id,String filePath){
        String fileName = filePath.substring(filePath.lastIndexOf("\\")+1);
        QueryWrapper wrapper = new QueryWrapper<>().eq("dec_file_name",fileName).eq("report_id",id);
        List<DecDocumentRec> decDocumentRecs =  decDocumentRecMapper.selectList(wrapper);
        DecDocumentRec decDocumentRec = null;
        if(null != decDocumentRecs && decDocumentRecs.size()>0){
            decDocumentRec =  decDocumentRecs.get(0);
            decDocumentRec.setDecFileName(fileName);
            decDocumentRec.setDecFileAddr(filePath);
            decDocumentRec.setCreateTime(DateUtil.formatDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
            decDocumentRec.setSbMsg("");
            decDocumentRec.setSbTime("");
            decDocumentRec.setIsSb("未申报");
            decDocumentRecMapper.updateById(decDocumentRec);
        }else{
            decDocumentRec = new DecDocumentRec();
            decDocumentRec.setReportId(id);
            decDocumentRec.setDecFileName(fileName);
            decDocumentRec.setDecFileAddr(filePath);
            decDocumentRec.setCreateTime(DateUtil.formatDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
            decDocumentRec.setSbMsg("");
            decDocumentRec.setSbTime("");
            decDocumentRec.setIsSb("未申报");
            decDocumentRecMapper.insert(decDocumentRec);
        }
    }

    @Override
    public String exportSubMapInfoFile(String targetFilePath,String fileName,List<AccBalMapInfoDto> accBalMapInfoDtos) {
        String msg = "";
        //获取模版路径
        String filePath = subMapMode.concat(File.separator).concat("科目余额表映射模版.xlsx");
        String jarPath = System.getProperty("java.class.path");
        String currentPath = "";
        if(jarPath.endsWith(".jar" ) && !jarPath.contains(";")){
            String jarDirectory = new File(jarPath).getAbsolutePath();
            currentPath = jarDirectory.substring(0,jarDirectory.lastIndexOf("\\"));
            filePath = currentPath.concat(filePath);
        }else{
            try {
                currentPath = new File(".").getCanonicalPath();
            } catch (IOException e) {
                e.printStackTrace();
            }
            filePath = currentPath.concat(filePath);
        }

        Path sourcePath = Paths.get(filePath);
        XSSFWorkbook wb = null;
        FileInputStream is = null;
        FileOutputStream fileOut = null;
        try {
            if (Files.exists(sourcePath)) {
                is = new FileInputStream(new File(filePath));
                wb = new XSSFWorkbook(is);
                Sheet sheet = wb.getSheetAt(0);
                // 循环写入数据
                for (int i = 0; i < accBalMapInfoDtos.size(); i++) {
                    AccBalMapInfoDto accBalMapInfoDto = accBalMapInfoDtos.get(i);
                    Row row = sheet.createRow(i+1);
                    Cell cell1 = row.createCell(0);
                    cell1.setCellValue(accBalMapInfoDto.getCompSubNo());
                    Cell cell2 = row.createCell(1);
                    cell2.setCellValue(accBalMapInfoDto.getCompSubName());
                    Cell cell3 = row.createCell(2);
                    cell3.setCellValue(accBalMapInfoDto.getSubNo());
                    Cell cell4 = row.createCell(3);
                    cell4.setCellValue(accBalMapInfoDto.getSubName());
                    Cell cell5 = row.createCell(4);
                    cell5.setCellValue(accBalMapInfoDto.getSubType());
                    Cell cell6 = row.createCell(5);
                    cell6.setCellValue(accBalMapInfoDto.getOpenType());
                    Cell cell7 = row.createCell(6);
                    cell7.setCellValue(String.valueOf(accBalMapInfoDto.getOpenBal()));
                    Cell cell8 = row.createCell(7);
                    cell8.setCellValue(String.valueOf(accBalMapInfoDto.getCurrentDebit()));
                    Cell cell9 = row.createCell(8);
                    cell9.setCellValue(String.valueOf(accBalMapInfoDto.getCurrentCredit()));
                    Cell cell10= row.createCell(9);
                    cell10.setCellValue(accBalMapInfoDto.getEndType());
                    Cell cell11= row.createCell(10);
                    cell11.setCellValue(String.valueOf(accBalMapInfoDto.getEndBal()));
                }

                // 保存修改后的工作簿到新文件
                fileOut = new FileOutputStream(targetFilePath);
                wb.write(fileOut);
                System.out.println("文件已成功保存到: " + targetFilePath);
            }
        } catch (IOException e) {
            msg="导出文件失败";
            e.printStackTrace();
        }finally {
            try {
                if (is!= null) {
                    is.close();
                }
                if (wb!= null) {
                    wb.close();
                }
                if(null!=fileOut) {
                    fileOut.close();
                }
            } catch (IOException e) {
                msg = "关闭文件异常！";
                e.printStackTrace();
            }
        }
        return msg;
    }

    /**
     * 初始化列
     * @param colNum
     * @param changed
     * @param row
     * @param rowColNum
     * @param entSubName
     * @param entFirtNum
     * @param entEndNum
     */
    private boolean initColumns(Row row,int[] rowColNum,int[] entSubName,int[] entFirtNum,int[] entEndNum,int realColumn, Map<String, List<String>> columnInfo) {

        int iNum = 0;
        int jNum = 0;
        int kNum = 0;
        int lNum = 0;
        boolean changed = false;
        for (int k = 0; k < row.getLastCellNum() ; k++) {
            if(null == row.getCell(k)){
                break;
            }
            String cellValue = getCellInfo(row.getCell(k),false);
            realColumn = k;
            if (StringUtils.isBlank(cellValue)) {
                break;
            }else{
                cellValue = cellValue.replaceAll("\\s", "").replaceAll("　", "").replaceAll("“-”","").replaceAll("“－”","");
            }

            if (null != row.getCell(k) && columnInfo.get("sorted").contains(cellValue)) {
                changed = true;
                if (iNum == 0) {
                    rowColNum[0] = k;
                } else {
                    rowColNum[1] = k;
                }
                iNum = 1;
            }

            if (null != row.getCell(k) && columnInfo.get("names").contains(cellValue)) {
                changed = true;
                if (jNum == 0) {
                    entSubName[0] = k;
                } else {
                    entSubName[1] = k;
                }
                jNum = 1;
            }

            if (null != row.getCell(k) && columnInfo.get("firtYears").contains(cellValue)) {
                changed = true;
                if (kNum == 0) {
                    entFirtNum[0] = k;
                } else {
                    entFirtNum[1] = k;
                }
                kNum = 1;
            }

            if (null != row.getCell(k) && columnInfo.get("endYears").contains(cellValue)) {
                changed = true;
                if (lNum == 0) {
                    entEndNum[0] = k;
                } else {
                    entEndNum[1] = k;
                }
                lNum = 1;
            }
        }
        return changed;
    }

    /**
     * 生成数据
     * @param row
     * @param rowColNum
     * @param entSubName
     * @param entFirtNum
     * @param entEndNum
     * @param types
     * @return
     */
    private SubMatching initSubMatching(Row row, int rowNo, int entSubName,String types, String prefix) {
        SubMatching matching =null;
        if (entSubName != -1) {
            String entName = "";
            if(null != row.getCell(entSubName)){
                entName = getCellInfo(row.getCell(entSubName),false).replaceAll("\\s", "").replaceAll("　","").replaceAll("“-”","").replaceAll("“－”","");
            }

            if (StringUtils.isNotBlank(entName)) {
                matching = new SubMatching();
                matching.setCompSubName(entName);

                if (rowNo != -1) {
                    String cellVal = "";
                    if(null !=row.getCell(rowNo)){
                        cellVal = getCellInfo(row.getCell(rowNo),false);
                    }

                    if(StringUtils.isBlank(cellVal)) {
                        matching.setCompSubNo(prefix.concat("$").concat(String.format("%03d", row.getRowNum())));
                    }else{
                        matching.setCompSubNo(prefix.concat("$").concat(cellVal));
                    }
                } else {
                    matching.setCompSubNo(prefix.concat(String.format("%03d", row.getRowNum())));
                }
                matching.setDecType(types);
            }
        }
        return matching;
    }

    /**
     * 生成数据
     * @param row
     * @param rowColNum
     * @param entSubName
     * @param entFirtNum
     * @param entEndNum
     * @param types
     * @return
     */
    private AssetAndLib initAssetAndLib(Row row,int rowColNum,int entSubName,int entFirtNum,int entEndNum,Map<String, SubMatchingDto> subMatchingMap,Map<String, StandardSub> standardSubMap,String id,int insNum,String prefix ) {
        AssetAndLib assetAndLib =null;
        String sortNo = "";
        if (rowColNum != -1) {
            String cellVal = "";
            if(null !=row.getCell(rowColNum)){
                cellVal = getCellInfo(row.getCell(rowColNum),false);
            }

            if(StringUtils.isBlank(cellVal)) {
                sortNo = prefix.concat("-").concat(String.format("%02d", row.getRowNum()));
            }else{
                sortNo = prefix.concat("#").concat(cellVal);
            }
        } else {
            sortNo = prefix.concat(String.format("%03d", row.getRowNum()));
        }

        if (entSubName != -1) {
            String entName = "";
            if(null != row.getCell(entSubName)){
                entName = getCellInfo(row.getCell(entSubName),false).replaceAll("\\s", "").replaceAll("　","").replaceAll("“-”","").replaceAll("“－”","");
            }

            if (StringUtils.isNotBlank(entName)) {

                SubMatchingDto matchingDto = subMatchingMap.get(sortNo.concat("#").concat(entName));
                if(null== matchingDto){
                    sortNo = sortNo.replace("#","$");
                    matchingDto = subMatchingMap.get(sortNo.concat("#").concat(entName));
                }
                if(null!= matchingDto && "禁用".equals(matchingDto.getSwitchFlg())){
                    return null;
                }

                if (null!= matchingDto) {
                    assetAndLib = new AssetAndLib();
                    assetAndLib.setReportId(id);
                    assetAndLib.setCompSubName(entName);
                    assetAndLib.setCompSubNo(matchingDto.getCompSubNo());
                    assetAndLib.setSubName(matchingDto.getSubName());
                    assetAndLib.setSubNo(matchingDto.getSubNo());
                }else{
                    SubMatching subMatching = this.initSubMatching(row, rowColNum, entSubName,"负债表科目",prefix);
                    this.fitSubString(subMatching,standardSubMap);
                    if("启用".equals(subMatching.getSwitchFlg())){
                        assetAndLib = new AssetAndLib();
                        assetAndLib.setReportId(id);
                        assetAndLib.setCompSubName(entName);
                        assetAndLib.setCompSubNo(subMatching.getCompSubNo());
                        assetAndLib.setSubName(subMatching.getSubName());
                        assetAndLib.setSubNo(subMatching.getSubNo());
                    }else{
                        return null;
                    }
                   insNum++;
                }

                if(null != assetAndLib){
                    if (entFirtNum != -1) {
                        String cellVal = "";
                        if(null !=row.getCell(entFirtNum)){
                            cellVal = getCellInfo(row.getCell(entFirtNum),true);
                        }

                        if(StringUtils.isNotBlank(cellVal)) {
                            assetAndLib.setOpenBal(new BigDecimal(cellVal));
                        }else{
                            assetAndLib.setOpenBal(new BigDecimal(0));
                        }
                    } else {
                        assetAndLib.setOpenBal(new BigDecimal(0));
                    }

                    if (entEndNum != -1) {
                        String cellVal = "";
                        if(null !=row.getCell(entEndNum)){
                            cellVal = getCellInfo(row.getCell(entEndNum),true);
                        }

                        if(StringUtils.isNotBlank(cellVal)) {
                            assetAndLib.setEndBal(new BigDecimal(cellVal));
                        }else{
                            assetAndLib.setEndBal(new BigDecimal(0));
                        }
                    } else {
                        assetAndLib.setEndBal(new BigDecimal(0));
                    }
                }
            }
        }
        return assetAndLib;
    }

    /**
     * 生成数据
     * @param row
     * @param rowColNum
     * @param entSubName
     * @param entFirtNum
     * @param entEndNum
     * @param types
     * @return
     */
    private IncomeStmInfo initIncomeStmInfo(Row row,int rowColNum,int entSubName,int entFirtNum,int entEndNum,Map<String, SubMatchingDto> subMatchingMap,Map<String, StandardSub> standardSubMap,String id,int insNum,String prefix) {
        IncomeStmInfo incomeStmInfo =null;

        String sortNo = "";
        if (rowColNum != -1) {
            String cellVal = "";
            if(null !=row.getCell(rowColNum)){
                cellVal = getCellInfo(row.getCell(rowColNum),false);
            }

            if(StringUtils.isBlank(cellVal)) {
                sortNo = prefix.concat("-").concat(String.format("%02d", row.getRowNum()));
            }else{
                sortNo = prefix.concat("#").concat(cellVal);
            }
        } else {
            sortNo = prefix.concat(String.format("%03d", row.getRowNum()));
        }

        if (entSubName != -1) {
            String entName = "";
            if(null != row.getCell(entSubName)){
                entName = getCellInfo(row.getCell(entSubName),false).replaceAll("\\s", "").replaceAll("　","").replaceAll("“-”","").replaceAll("“－”","");
            }

            if (StringUtils.isNotBlank(entName)) {
                SubMatchingDto matchingDto = subMatchingMap.get(sortNo.concat("#").concat(entName));
                if(null== matchingDto){
                    sortNo = sortNo.replace("#","$");
                    matchingDto = subMatchingMap.get(sortNo.concat("#").concat(entName));
                }
                if(null!= matchingDto && "禁用".equals(matchingDto.getSwitchFlg())){
                    return null;
                }
                if (null!= matchingDto ) {
                    incomeStmInfo = new IncomeStmInfo();
                    incomeStmInfo.setReportId(id);
                    incomeStmInfo.setCompSubName(entName);
                    incomeStmInfo.setCompSubNo(matchingDto.getCompSubNo());
                    incomeStmInfo.setSubName(matchingDto.getSubName());
                    incomeStmInfo.setSubNo(matchingDto.getSubNo());
                }else{
                    SubMatching subMatching = this.initSubMatching(row, rowColNum, entSubName,"利润表科目",prefix);
                    this.fitSubString(subMatching,standardSubMap);
                    if("启用".equals(subMatching.getSwitchFlg())){
                        incomeStmInfo = new IncomeStmInfo();
                        incomeStmInfo.setReportId(id);
                        incomeStmInfo.setCompSubName(entName);
                        incomeStmInfo.setCompSubNo(subMatching.getCompSubNo());
                        incomeStmInfo.setSubName(subMatching.getSubName());
                        incomeStmInfo.setSubNo(subMatching.getSubNo());
                    }
                    insNum++;
                }

                if(null != incomeStmInfo){
                    if (entFirtNum != -1) {
                        String cellVal = "";
                        if(null !=row.getCell(entFirtNum)){
                            cellVal = getCellInfo(row.getCell(entFirtNum),true);
                        }

                        if(StringUtils.isNotBlank(cellVal)) {
                            incomeStmInfo.setCurrentAmt(new BigDecimal(cellVal));
                        }else{
                            incomeStmInfo.setCurrentAmt(new BigDecimal(0));
                        }
                    } else {
                        incomeStmInfo.setCurrentAmt(new BigDecimal(0));
                    }

                    if (entEndNum != -1) {
                        String cellVal = "";
                        if(null !=row.getCell(entEndNum)){
                            cellVal = getCellInfo(row.getCell(entEndNum),true);
                        }

                        if(StringUtils.isNotBlank(cellVal)) {
                            incomeStmInfo.setPreviousAmt(new BigDecimal(cellVal));
                        }else{
                            incomeStmInfo.setPreviousAmt(new BigDecimal(0));
                        }
                    } else {
                        incomeStmInfo.setPreviousAmt(new BigDecimal(0));
                    }
                }
            }
        }
        return incomeStmInfo;
    }

    @Override
    public String initAccXlsx(File file,String decYear,String pSubType, Map<String, List<String>> columnInfo) {
        String rtrVal = "";
        InputStream fis = null;
        Workbook workbook = null;

        //导入主数据
        String id = this.mainDataAction(decYear,pSubType,false);
        try {
            if (null == file){
                return "文件未选择！";
            }

            fis = new FileInputStream(file);
            workbook = WorkbookFactory.create(fis);

            int sheetCount = workbook.getNumberOfSheets();
            int insNumber = 0;
            LambdaQueryWrapper<AssetAndLib> wrapperDelete = new LambdaQueryWrapper();
            wrapperDelete.eq(AssetAndLib::getReportId, id);
            assetAndLibService.getBaseMapper().delete(wrapperDelete);
            LambdaQueryWrapper<IncomeStmInfo> wrapperDelete1 = new LambdaQueryWrapper();
            wrapperDelete1.eq(IncomeStmInfo::getReportId, id);
            incomeStmInfoService.getBaseMapper().delete(wrapperDelete1);
            for(int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowCount = sheet.getLastRowNum();
                int numMerger =   sheet.getNumMergedRegions();
                int realColumn = 0 ;

                //行次
                int[] rowColNum = {-1,-1};
                //企业科目名称
                int[] entSubName = {-1,-1};
                //期初额（本年）
                int[] entFirtNum = {-1,-1};
                //期末额（上年）
                int[] entEndNum = {-1,-1};
                if(sheet.getSheetName().contains("资产")) {
                    List<SubMatchingDto> subMatchingDtos =  subMatchingMapper.selectAllData("负债表科目");
                    Map<String, SubMatchingDto> subMatchingMap = subMatchingDtos.stream().collect(Collectors.toMap(SubMatchingDto::getCbName, subMatchingDto ->subMatchingDto));
                    LambdaQueryWrapper<StandardSub> wrapper = new LambdaQueryWrapper<StandardSub>();
                    wrapper.eq(StandardSub::getDecType, "负债表科目");
                    List<StandardSub> standardSubs = standardSubMapper.selectList(wrapper);
                    Map<String, StandardSub> standardSubMap = standardSubs.stream().collect(Collectors.toMap(StandardSub::getSubName, standardSub -> standardSub));

                    //检查行次是否存在
                    boolean changed = false;
                    List<AssetAndLib> assetAndLibs = new ArrayList<AssetAndLib>();
                    List<AssetAndLib> assetAndLibs1 = new ArrayList<AssetAndLib>();
                    for (int j = 0; j <= rowCount; j++) {
                        Row row = sheet.getRow(j);
                        if (!changed) {
                            //初始化列
                            changed =  this.initColumns(row,rowColNum,entSubName,entFirtNum,entEndNum,realColumn, columnInfo);

                        }else{
                            if(null == row){
                                break;
                            }
                            if(null == row.getCell(entSubName[0])){
                                break;
                            }
                            if(getCellInfo(row.getCell(entSubName[0]),false).contains("注:") || getCellInfo(row.getCell(entSubName[0]),false).contains("注：")) {
                                break;
                            }
                            AssetAndLib assetAndLib = this.initAssetAndLib(row,rowColNum[0],entSubName[0],entFirtNum[0],entEndNum[0],subMatchingMap,standardSubMap,id,insNumber,"80");
                            if(null != assetAndLib){
                                assetAndLibs.add(assetAndLib);
                            }

                            //第二组数据
                            if(entSubName[1] != -1 ){
                                AssetAndLib assetAndLib1 = this.initAssetAndLib(row,rowColNum[1], entSubName[1],entFirtNum[1],entEndNum[1],subMatchingMap,standardSubMap,id,insNumber,"81");
                                if(null != assetAndLib1){
                                    assetAndLibs1.add(assetAndLib1);
                                }
                            }
                        }
                    }
                    assetAndLibs.addAll(assetAndLibs1);
                    assetAndLibService.saveBatch(assetAndLibs);
                }else if(sheet.getSheetName().contains("利润")){
                    List<SubMatchingDto> subMatchingDtos =  subMatchingMapper.selectAllData("利润表科目");
                    Map<String, SubMatchingDto> subMatchingMap = subMatchingDtos.stream().collect(Collectors.toMap(SubMatchingDto::getCbName, subMatchingDto ->subMatchingDto));
                    LambdaQueryWrapper<StandardSub> wrapper = new LambdaQueryWrapper<StandardSub>();
                    wrapper.eq(StandardSub::getDecType, "利润表科目");
                    List<StandardSub> standardSubs = standardSubMapper.selectList(wrapper);
                    Map<String, StandardSub> standardSubMap = standardSubs.stream().collect(Collectors.toMap(StandardSub::getSubName, standardSub -> standardSub));

                    //检查行次是否存在
                    boolean changed = false;
                    List<IncomeStmInfo> incomeStmInfos = new ArrayList<IncomeStmInfo>();
                    List<IncomeStmInfo> incomeStmInfos1= new ArrayList<IncomeStmInfo>();
                    for (int j = 0; j <= rowCount; j++) {
                        Row row = sheet.getRow(j);
                        if (!changed) {
                            //初始化列
                            changed =  this.initColumns(row,rowColNum,entSubName,entFirtNum,entEndNum,realColumn, columnInfo);

                        }else{
                            if(null == row){
                                break;
                            }
                            if(null == row.getCell(entSubName[0])){
                                break;
                            }
                            if(getCellInfo(row.getCell(entSubName[0]),false).contains("注:") || getCellInfo(row.getCell(entSubName[0]),false).contains("注：")) {
                                break;
                            }
                            IncomeStmInfo IncomeStmInfo = this.initIncomeStmInfo(row,rowColNum[0],entSubName[0],entFirtNum[0],entEndNum[0],subMatchingMap,standardSubMap,id,insNumber,"90");
                            if(null != IncomeStmInfo){
                                incomeStmInfos.add(IncomeStmInfo);
                            }

                            //第二组数据
                            if(entSubName[1] != -1 ){
                                IncomeStmInfo IncomeStmInfo1 = this.initIncomeStmInfo(row,rowColNum[1], entSubName[1],entFirtNum[1],entEndNum[1],subMatchingMap,standardSubMap,id,insNumber,"91");
                                if(null != IncomeStmInfo1){
                                    incomeStmInfos1.add(IncomeStmInfo1);
                                }
                            }
                        }
                    }
                    incomeStmInfos.addAll(incomeStmInfos1);
                    incomeStmInfoService.saveBatch(incomeStmInfos);
                }else{
                    rtrVal = sheet.getSheetName().concat("#").concat(rtrVal);
                }
            }
            if(StringUtils.isNotBlank(rtrVal)){
                rtrVal = "0【".concat(rtrVal).concat("】,请检查Excel表格sheet名称，只有带有【资产负债表】和【利润表】的sheet数据才能导入");
            }

        } catch (Exception e) {
            e.printStackTrace();
            rtrVal = "导入数据失败";
        }finally {
            try {
                workbook.close();
                workbook = null;
                fis.close();
                fis = null;

            } catch (IOException e) {
                e.printStackTrace();
                rtrVal = "关闭Excel文件失败";
            }
        }
        return rtrVal;
    }

    @Override
    public String exportIncFile(String targetFilePath, String fileName, List<IncomeStmInfoDto> incomeStmInfos) {
        String msg = "";
        //获取模版路径
        String filePath = subMapMode.concat(File.separator).concat("利润表映射模版.xlsx");
        String jarPath = System.getProperty("java.class.path");
        String currentPath = "";
        if(jarPath.endsWith(".jar" ) && !jarPath.contains(";")){
            String jarDirectory = new File(jarPath).getAbsolutePath();
            currentPath = jarDirectory.substring(0,jarDirectory.lastIndexOf("\\"));
            filePath = currentPath.concat(filePath);
        }else{
            try {
                currentPath = new File(".").getCanonicalPath();
            } catch (IOException e) {
                e.printStackTrace();
            }
            filePath = currentPath.concat(filePath);
        }

        Path sourcePath = Paths.get(filePath);
        XSSFWorkbook wb = null;
        FileInputStream is = null;
        FileOutputStream fileOut = null;
        try {
            if (Files.exists(sourcePath)) {
                is = new FileInputStream(new File(filePath));
                wb = new XSSFWorkbook(is);
                Sheet sheet = wb.getSheetAt(0);
                // 循环写入数据
                for (int i = 0; i < incomeStmInfos.size(); i++) {
                    IncomeStmInfoDto incomeStmInfoDto = incomeStmInfos.get(i);
                    Row row = sheet.createRow(i+1);
                    Cell cell1 = row.createCell(0);
                    cell1.setCellValue(incomeStmInfoDto.getCompSubNo());
                    Cell cell2 = row.createCell(1);
                    cell2.setCellValue(incomeStmInfoDto.getCompSubName());
                    Cell cell3 = row.createCell(2);
                    cell3.setCellValue(incomeStmInfoDto.getSubNo());
                    Cell cell4 = row.createCell(3);
                    cell4.setCellValue(incomeStmInfoDto.getSubName());
                    Cell cell5 = row.createCell(4);
                    cell5.setCellValue(incomeStmInfoDto.getSubType());
                    Cell cell6 = row.createCell(5);
                    cell6.setCellValue(String.valueOf(incomeStmInfoDto.getCurrentAmt()));
                    Cell cell7 = row.createCell(6);
                    cell7.setCellValue(String.valueOf(incomeStmInfoDto.getPreviousAmt()));
                  }

                // 保存修改后的工作簿到新文件
                fileOut = new FileOutputStream(targetFilePath);
                wb.write(fileOut);
                System.out.println("文件已成功保存到: " + targetFilePath);
            }
        } catch (IOException e) {
            msg="导出文件失败";
            e.printStackTrace();
        }finally {
            try {
                if (is!= null) {
                    is.close();
                }
                if (wb!= null) {
                    wb.close();
                }
                if(null!=fileOut) {
                    fileOut.close();
                }
            } catch (IOException e) {
                msg = "关闭文件异常！";
                e.printStackTrace();
            }
        }
        return msg;
    }

    @Override
    public String exportAssetFile(String targetFilePath, String fileName, List<AssetAndLibDto> assetAndLibDtos) {
        String msg = "";
        //获取模版路径
        String filePath = subMapMode.concat(File.separator).concat("资产负债表映射模版.xlsx");
        String jarPath = System.getProperty("java.class.path");
        String currentPath = "";
        if(jarPath.endsWith(".jar" ) && !jarPath.contains(";")){
            String jarDirectory = new File(jarPath).getAbsolutePath();
            currentPath = jarDirectory.substring(0,jarDirectory.lastIndexOf("\\"));
            filePath = currentPath.concat(filePath);
        }else{
            try {
                currentPath = new File(".").getCanonicalPath();
            } catch (IOException e) {
                e.printStackTrace();
            }
            filePath = currentPath.concat(filePath);
        }

        Path sourcePath = Paths.get(filePath);
        XSSFWorkbook wb = null;
        FileInputStream is = null;
        FileOutputStream fileOut = null;
        try {
            if (Files.exists(sourcePath)) {
                is = new FileInputStream(new File(filePath));
                wb = new XSSFWorkbook(is);
                Sheet sheet = wb.getSheetAt(0);
                // 循环写入数据
                for (int i = 0; i < assetAndLibDtos.size(); i++) {
                    AssetAndLibDto assetAndLibDto = assetAndLibDtos.get(i);
                    Row row = sheet.createRow(i+1);
                    Cell cell1 = row.createCell(0);
                    cell1.setCellValue(assetAndLibDto.getCompSubNo());
                    Cell cell2 = row.createCell(1);
                    cell2.setCellValue(assetAndLibDto.getCompSubName());
                    Cell cell3 = row.createCell(2);
                    cell3.setCellValue(assetAndLibDto.getSubNo());
                    Cell cell4 = row.createCell(3);
                    cell4.setCellValue(assetAndLibDto.getSubName());
                    Cell cell5 = row.createCell(4);
                    cell5.setCellValue(assetAndLibDto.getSubType());
                    Cell cell6 = row.createCell(5);
                    cell6.setCellValue(String.valueOf(assetAndLibDto.getOpenBal()));
                    Cell cell7 = row.createCell(6);
                    cell7.setCellValue(String.valueOf(assetAndLibDto.getEndBal()));
                }

                // 保存修改后的工作簿到新文件
                fileOut = new FileOutputStream(targetFilePath);
                wb.write(fileOut);
                System.out.println("文件已成功保存到: " + targetFilePath);
            }
        } catch (IOException e) {
            msg="导出文件失败";
            e.printStackTrace();
        }finally {
            try {
                if (is!= null) {
                    is.close();
                }
                if (wb!= null) {
                    wb.close();
                }
                if(null!=fileOut) {
                    fileOut.close();
                }
            } catch (IOException e) {
                msg = "关闭文件异常！";
                e.printStackTrace();
            }
        }
        return msg;
    }

    private void initMapInfo() {
        this.fileMap.put("年度".concat("财务报表2007旧准则.xlsx"),"com.taxinfo.service.impl.data.YearFinancial2007OldStandard");
        this.fileMap.put("年度".concat("财务报表2013小企业会计准则.xlsx"),"com.taxinfo.service.impl.data.YearAccStandards2013SmallEnterprise");
        this.fileMap.put("年度".concat("财务报表2019新准则.xlsx"),"com.taxinfo.service.impl.data.YearNewFinancialReport2019Standards");
        this.fileMap.put("年度".concat("企业所得税年度纳税申报表（A类）.xlsx"),"com.taxinfo.service.impl.data.YearAnnCorporateIncomeTaxDeclaration");
        this.fileMap.put("季度".concat("财务报表2007旧准则.xlsx"),"com.taxinfo.service.impl.data.QuartFinancial2007OldStandard");
        this.fileMap.put("季度".concat("财务报表2013小企业会计准则.xlsx"),"com.taxinfo.service.impl.data.QuartAccStandards2013SmallEnterprise");
        this.fileMap.put("季度".concat("财务报表2019新准则.xlsx"),"com.taxinfo.service.impl.data.QuartNewFinancialReport2019Standards");
        this.fileMap.put("季度".concat("企业所得税月（季）度预缴纳税申报表（A类）.xlsx"),"com.taxinfo.service.impl.data.QuartEntIncomeTaxPrepaidTaxDecMonth");
        this.fileMap.put("月度".concat("财务报表2007旧准则.xlsx"),"com.taxinfo.service.impl.data.MonthFinancial2007OldStandard");
        this.fileMap.put("月度".concat("财务报表2013小企业会计准则.xlsx"),"com.taxinfo.service.impl.data.MonthAccStandards2013SmallEnterprise");
        this.fileMap.put("月度".concat("财务报表2019新准则.xlsx"),"com.taxinfo.service.impl.data.MonthNewFinancialReport2019Standards");
    }

    /**
     * 获取行号与列号
     *
     * @return
     */
    private List<AccBalMapInfo>  getRowColumInfo(SoftwareMapModel softwareMapModel, Sheet sheet,Map<String, SubMatchingDto> subMatchingMap) {
        String charSubNo = softwareMapModel.getDestSubNo();
        String charSubName = softwareMapModel.getDestSubName();
        String charOpenBal = softwareMapModel.getDestOpenBal();
        String charCurrentDebit = softwareMapModel.getDestCurrentDebit();
        String charCurrentCredit = softwareMapModel.getDestCurrentCredit();
        String charCloseBal = softwareMapModel.getDestEndBal();
        String[] charOpenBals = charOpenBal.split("-");
        String[] charCurrentDebits = charCurrentDebit.split("-");
        String[] charCurrentCredits = charCurrentCredit.split("-");
        String[] charCloseBals = charCloseBal.split("-");
        //确认是否首行（标题行）
        int titleRowNum = 0;
        int titleSubNoColNum = 0;
        int titleSubNameColNum = 0;
        int titleOpenBalColNum = 0;
        int titleCurrentDebitColNum = 0;
        int titleCurrentCreditColNum = 0;
        int titleCloseBalColNum = 0;
        int titleOpenTypeColNum = 0;
        int titleCloseTypeColNum = 0;

        int titleOpenBalColNum2 = 0;
        int titleCloseBalColNum2 = 0;

        boolean nextline = false;
        for (Row row : sheet) {
            if(nextline){
                nextline = false;
                continue;
            }
            int charSubNoInt = 0;
            int charSubNameInt = 0;
            int charOpenBalInt = 0;
            int charCurrentDebitInt = 0;
            int charCurrentCreditInt = 0;
            int charCloseBalInt = 0;
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue().trim();
                    if (cellValue.equals(charSubNo)) {
                        charSubNoInt = row.getRowNum() + 1;
                        titleSubNoColNum = cell.getColumnIndex();
                    } else if (cellValue.equals(charSubName)) {
                        charSubNameInt = row.getRowNum() + 1;
                        titleSubNameColNum = cell.getColumnIndex();
                    } else if (charOpenBal.contains(cellValue)) {
                        charOpenBalInt = row.getRowNum() + 1;
                        titleOpenBalColNum = cell.getColumnIndex();
                        titleOpenTypeColNum = titleOpenBalColNum-1;
                        if(charOpenBals.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if (cellValueNext.equals(charOpenBals[1]) && (nextCell.getColumnIndex() ==titleOpenBalColNum || nextCell.getColumnIndex() == titleOpenBalColNum+1)) {
                                    charOpenBalInt = nextRow.getRowNum() + 1;
                                    titleOpenBalColNum = nextCell.getColumnIndex();
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                    if(nextCell.getColumnIndex() == titleOpenBalColNum+1){
                                        titleOpenTypeColNum = nextCell.getColumnIndex() -2;
                                    }
                                    break;
                                }
                            }
                        }else{
                            if(charOpenBals[0].contains("*")){
                                Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                                for (Cell nextCell : nextRow){
                                    String cellValueNext = nextCell.getStringCellValue().trim();
                                    if (cellValueNext.equals("借方") && (nextCell.getColumnIndex() ==titleOpenBalColNum )) {
                                        nextline = true;
                                        charCloseBalInt = nextRow.getRowNum() + 1;
                                        charSubNameInt = nextRow.getRowNum() + 1;
                                        charSubNoInt = nextRow.getRowNum() + 1;
                                        titleOpenBalColNum = titleOpenBalColNum;
                                        titleOpenBalColNum2 = titleOpenBalColNum+1;
                                        break;
                                    }else if(cellValueNext.equals("贷方") && (nextCell.getColumnIndex() ==titleOpenBalColNum )) {
                                        nextline = true;
                                        charCloseBalInt = nextRow.getRowNum() + 1;
                                        charSubNameInt = nextRow.getRowNum() + 1;
                                        charSubNoInt = nextRow.getRowNum() + 1;
                                        titleOpenBalColNum = titleOpenBalColNum+1;
                                        titleOpenBalColNum2 = titleOpenBalColNum;
                                        break;
                                    }else{

                                    }
                                }
                            }
                        }

                    } else if (charCurrentDebit.contains(cellValue)) {
                        charCurrentDebitInt = row.getRowNum() + 1;
                        titleCurrentDebitColNum = cell.getColumnIndex();

                        if(charCurrentDebits.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if(charCurrentDebits[0].equals(charCurrentCredits[0])){
                                    titleCurrentCreditColNum = cell.getColumnIndex();
                                    if (cellValueNext.equals(charCurrentDebits[1]) && (nextCell.getColumnIndex() == titleCurrentDebitColNum || nextCell.getColumnIndex() == titleCurrentDebitColNum+1)) {
                                        charCurrentDebitInt = nextRow.getRowNum() + 1;
                                        titleCurrentDebitColNum = nextCell.getColumnIndex();
                                    }else if (cellValueNext.equals(charCurrentCredits[1]) && (nextCell.getColumnIndex() == titleCurrentCreditColNum || nextCell.getColumnIndex() == titleCurrentCreditColNum+1)) {
                                        charCurrentCreditInt = nextRow.getRowNum() + 1;
                                        titleCurrentCreditColNum = nextCell.getColumnIndex();
                                    }
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                }else{
                                    if (cellValueNext.equals(charCurrentDebits[1]) && (nextCell.getColumnIndex() == titleCurrentDebitColNum || nextCell.getColumnIndex() == titleCurrentDebitColNum+1)) {
                                        charCurrentDebitInt = nextRow.getRowNum() + 1;
                                        titleCurrentDebitColNum = nextCell.getColumnIndex();
                                        charSubNameInt = nextRow.getRowNum() + 1;
                                        charSubNoInt = nextRow.getRowNum() + 1;
                                        nextline=true;
                                        break;
                                    }
                                }

                            }
                        }
                    } else if (charCurrentCredit.contains(cellValue)) {
                        charCurrentCreditInt = row.getRowNum() + 1;
                        titleCurrentCreditColNum = cell.getColumnIndex();
                        if(charCurrentCredits.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if (cellValueNext.equals(charCurrentCredits[1]) && (nextCell.getColumnIndex() == titleCurrentCreditColNum || nextCell.getColumnIndex() == titleCurrentCreditColNum+1)) {
                                    charCurrentCreditInt = nextRow.getRowNum() + 1;
                                    titleCurrentCreditColNum = nextCell.getColumnIndex();
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                    break;
                                }
                            }
                        }
                    } else if (charCloseBal.contains(cellValue)) {
                        charCloseBalInt = row.getRowNum() + 1;
                        titleCloseBalColNum = cell.getColumnIndex();
                        titleCloseTypeColNum = titleCloseBalColNum -1;
                        if(charCloseBals.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if (cellValueNext.equals(charCloseBals[1]) && (nextCell.getColumnIndex() == titleCloseBalColNum || nextCell.getColumnIndex() == titleCloseBalColNum+1)) {
                                    charCloseBalInt = nextRow.getRowNum() + 1;
                                    titleCloseBalColNum = nextCell.getColumnIndex();
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                    if(nextCell.getColumnIndex() == titleCloseBalColNum+1){
                                        titleCloseTypeColNum = nextCell.getColumnIndex() -2;
                                    }
                                    break;
                                }
                            }
                        }else{

                            if(charCloseBals[0].contains("*")){
                                Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                                for (Cell nextCell : nextRow){
                                    String cellValueNext = nextCell.getStringCellValue().trim();
                                    if (cellValueNext.equals("借方") && (nextCell.getColumnIndex() == titleCloseBalColNum )){
                                        charCloseBalInt = nextRow.getRowNum() + 1;
                                        charSubNameInt = nextRow.getRowNum() + 1;
                                        charSubNoInt = nextRow.getRowNum() + 1;
                                        titleCloseBalColNum = nextCell.getColumnIndex();
                                        titleCloseBalColNum2 = nextCell.getColumnIndex()+1;
                                        nextline=true;
                                        break;
                                    }else if (cellValueNext.equals("贷方") && (nextCell.getColumnIndex() == titleCloseBalColNum )){
                                        charCloseBalInt = nextRow.getRowNum() + 1;
                                        charSubNameInt = nextRow.getRowNum() + 1;
                                        charSubNoInt = nextRow.getRowNum() + 1;
                                        titleCloseBalColNum = nextCell.getColumnIndex()+1;
                                        titleCloseBalColNum2 = nextCell.getColumnIndex();
                                        nextline=true;
                                        break;
                                    }else{

                                    }
                                }
                            }
                        }
                    }
                }

                if(cell.getColumnIndex()+1 == row.getLastCellNum()){
                    break;
                }
            }

            if (charSubNoInt == charSubNameInt
//                    && charSubNoInt == charOpenBalInt
                    && charSubNoInt == charCurrentDebitInt
                    && charSubNoInt == charCurrentCreditInt
//                    && charSubNoInt == charCloseBalInt
                    && charSubNoInt != 0 ) {
                //标题行
                titleRowNum = charSubNoInt;
                break;
            }
        }

        if(titleRowNum == 0){
            return null; // 未找到匹配项
        }

        //获取最后行号
        int lastNum = sheet.getLastRowNum();
        for (int i = lastNum; i > 0; i--) {
            Row row = sheet.getRow(i);
            if (null != row.getCell(titleSubNoColNum)) {
                try{
                    Double.valueOf(String.valueOf(row.getCell(titleSubNoColNum)));
                }catch (Exception e){
                    continue;
                }
                lastNum = row.getRowNum();
                break;
            }
        }
        List<AccBalMapInfo> accBalMapInfos  = new ArrayList<AccBalMapInfo>();
        for(int rowNumber = titleRowNum; rowNumber<=lastNum; rowNumber++) {
            Row row = sheet.getRow(rowNumber);
            AccBalMapInfo balMapInfo = new AccBalMapInfo();
            balMapInfo.setCompSubNo(getCellInfo(row.getCell(titleSubNoColNum),false));
            balMapInfo.setCompSubName(getCellInfo(row.getCell(titleSubNameColNum),false));
            if(charOpenBals[0].contains("*")){
                SubMatchingDto matchingDto =  subMatchingMap.get(balMapInfo.getCompSubNo());
                BigDecimal first = new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum),true));
                BigDecimal sencond = new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum2),true));
                if("资产类".equals(matchingDto.getSubType())){
                    if(sencond.compareTo(new BigDecimal(0)) != 0 && first.compareTo(new BigDecimal(0)) ==0){
                        balMapInfo.setOpenBal(new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum2),true)));
                    }else{
                        balMapInfo.setOpenBal(new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum),true)));
                    }
                    balMapInfo.setOpenType("借");
                }else if("负债类".equals(matchingDto.getSubType())){
                    if(first.compareTo(new BigDecimal(0)) != 0 && sencond.compareTo(new BigDecimal(0)) ==0){
                        balMapInfo.setOpenBal(new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum),true)));
                    }else{
                        balMapInfo.setOpenBal(new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum2),true)));
                    }
                    balMapInfo.setOpenType("贷");
                }else{
                    balMapInfo.setOpenBal(new BigDecimal(0));
                }

            }else{
                balMapInfo.setOpenType(getCellInfo(row.getCell(titleOpenTypeColNum),false));
                balMapInfo.setOpenBal(new BigDecimal(getCellInfo(row.getCell(titleOpenBalColNum),true)));
            }

            System.out.println("### =".concat(row.getCell(titleSubNoColNum).toString()).concat(":".concat(getCellInfo(row.getCell(titleCurrentDebitColNum),true))));
            balMapInfo.setCurrentDebit(new BigDecimal(getCellInfo(row.getCell(titleCurrentDebitColNum),true)));
            balMapInfo.setCurrentCredit(new BigDecimal(getCellInfo(row.getCell(titleCurrentCreditColNum),true)));

            if(charCloseBals[0].contains("*")){
                SubMatchingDto matchingDto =  subMatchingMap.get(balMapInfo.getCompSubNo());
                BigDecimal first = new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum),true));
                BigDecimal sencond = new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum2),true));
                if("资产类".equals(matchingDto.getSubType())){
                    balMapInfo.setEndType("借");
                    if(sencond.compareTo(new BigDecimal(0)) != 0 && first.compareTo(new BigDecimal(0)) ==0){
                        balMapInfo.setEndBal(new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum2),true)));
                    }else{
                        balMapInfo.setEndBal(new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum),true)));
                    }

                }else if("负债类".equals(matchingDto.getSubType())){
                    if(first.compareTo(new BigDecimal(0)) != 0 && sencond.compareTo(new BigDecimal(0)) ==0){
                        balMapInfo.setEndBal(new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum),true)));
                    }else{
                        balMapInfo.setEndBal(new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum2),true)));
                    }
                    balMapInfo.setEndType("贷");
                }else{
                    balMapInfo.setEndBal(new BigDecimal(0));
                }
            }else{
                balMapInfo.setEndType(getCellInfo(row.getCell(titleCloseTypeColNum),false));
                balMapInfo.setEndBal(new BigDecimal(getCellInfo(row.getCell(titleCloseBalColNum),true)));
            }

            accBalMapInfos.add(balMapInfo);
        }
        return accBalMapInfos;
    }

    /**
     * 获取单元格信息
     * @param cell
     * @return
     */
    private String getCellInfo(Cell cell,boolean chkflg) {
        String cellValue = "";
        if(null != cell){
            if (cell.getCellType() == CellType.STRING) {
                cellValue = cell.getStringCellValue().replace(" ","").replace(",","").replaceAll("“-”","").replaceAll("“－”","").trim();
            }else if (cell.getCellType() == CellType.NUMERIC) {
                // 将科学计数法转换为常规数字格式
                double value = cell.getNumericCellValue();
                cellValue = new DecimalFormat("###.##").format(value); // 格式化为常规数字格式
            }else{
                ;
            }
        }
        if(StringUtils.isBlank(cellValue) && chkflg){
            return "0";
        }else{
            return cellValue;
        }

    }

    /**
     * @author wanghy
     */
    @Transactional
    public static interface ExcelUpdateInterface {
        /**
         *
         * @param wb workbook
         * @param accBalMapInfos 映射的数据集合
         * @param type 类型 :年度、季度、月度
         */
        public String businessLogic(XSSFWorkbook wb,  Map<String,List> dataInfos ,String sblx,String sbYear);
    }

    /**
     * 处理数据
     * @param subMatchings
     * @param standardSubMap
     */
    private void fitSubString(SubMatching subMatching,Map<String, StandardSub> standardSubMap){
        String compSubName =subMatching.getCompSubName();
        StandardSub standardSub = standardSubMap.get(compSubName);
        if (null != standardSub) {
            subMatching.setSubNo(standardSub.getSubNo());
            subMatching.setSubName(standardSub.getSubName());
            subMatching.setSubType(standardSub.getSubType());
        } else {
            subMatching.setSubNo("");
            subMatching.setSubName("");
            subMatching.setSubType("");
        }
        if (StringUtils.isBlank(subMatching.getSubName()) && StringUtils.isBlank(subMatching.getSubNo())) {
            subMatching.setSwitchFlg("禁用");
        } else {
            subMatching.setSwitchFlg("启用");
        }
        subMatching.setStatus("1");
        subMatchingMapper.insert(subMatching);
    }
}


